var express = require("express");
var uunewid = require("uuid");
var router = express.Router();
var moment = require("moment");
const { checkProperty, checkAuth } = require("../utils/utils");
var constants = require("../utils/constants");
let log = require("cf-nodejs-logging-support");
log.setLoggingLevel("info");

const { ohana } = require("ohana-node-orm");

function checkData(res, data) {
  if (data == null) {
    res.send(500);
  }
}

const tableName = "SAP_JIANGDU_JINRONGHUIQIS";
const tagTableName = "SAP_JIANGDU_TAG_FINS";



router.get("/getFinStatistics", function (req, res, next) {
  const user = checkAuth(req.user, constants.BASICAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
  }
  getFinStatistics().then(result => {
    res.send(result)
  })

})
async function getFinStatistics() {

  let ohana_instance = new ohana(tableName);
  let result = await ohana_instance.raw(
    "select BANK_NAME, count(*) as TOTAL from " + tableName + " where BANK_NAME is not NULL group by BANK_NAME order by TOTAL desc  limit 5"
  );
  var resultData = {};
  resultData.topBank = result;
  //monthly growth
  var dateQuery = "select count(*) as TOTAL from " + tableName + " where CREATED_AT between '" + moment().days(-30).format("YYYY-MM") + "' and '" + moment().format("YYYY-MM") + "'"
  console.log(dateQuery)
  var monthlygrowth = await ohana_instance.raw(dateQuery);
  resultData.monthlygrowth = monthlygrowth[0].TOTAL;
  return resultData;
}

router.get("/count", async (req, res, next) => {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '计数'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  let { USER_ID } = req.query;
  getCount(USER_ID)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getCount(USER_ID) {
  let result = null;
  let ohana_instance = new ohana(tableName);
  if (USER_ID) {
    result = await ohana_instance.raw(
      "select count(*) as total from " +
      tableName +
      " where USER_ID_USER_ID = '" +
      USER_ID +
      "'"
    );
  } else {
    result = await ohana_instance.raw(
      "select count(*) as total from " + tableName
    );
  }
  return result[0];
}

router.post("/", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '获取Fin.没有back 参数为 所有已发布的金融惠企政策。'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/fin_get"
                      }  
                  },
              }
      } */

  const userNew = checkAuth(req.user, constants.FINAUTH);
  const { type } = req.body;
  const { data } = req.body;
  const { page } = req.body.data;
  const fin = new ohana(tableName); // new ohana('table_name');
  if (!type || userNew.admin == true) {
    var condition = {};
    if (userNew.admin == false) {
      condition.STATUS = constants.STATUSON;
    }
    if (!data || !data.NAME) {
      //FIN_ID 取 单个asset
      if (data.FIN_ID) {
        condition.FIN_ID = data.FIN_ID;
        condition.STATUS = constants.STATUSON;
        fin
          .findOne(condition)
          .then((result) => {
            if (result.length > 0) {
              res.send(result);
            } else {
              res.send({
                message: constants.MESSAGEWARNINGSTATUSOFF,
                code: -1,
              });
            }
          })
          .catch((err) => {
            log.error(err);
            res.status(500).send(err);
          });
      } else {
        //取所有
        var sqlText = "select * from " + tableName;
        if (userNew.admin == false) {
          sqlText += " where STATUS = '" + constants.STATUSON + "'";
        }
        if (page) {
          const pageOffset = page.number * page.page;
          var pageText = " limit " + page.number + " offset " + pageOffset;
          sqlText += pageText;
        }
        // console.log(sqlText);
        fin
          .raw(sqlText)
          .then((result) => {
            res.send(result);
          })
          .catch((err) => {
            log.error(err);
            res.status(500).send(err);
          });
      }
    } else {
      var sqlText =
        "select * from " + tableName + " where NAME like '%" + data.NAME + "%'";
      //如果是admin 略过
      if (userNew.admin == false) {
        //非管理员 只返回 状态是 on的
        sqlText += " and STATUS = '" + constants.STATUSON + "'";
      }
      // STATUS = '" + constants.STATUSON + "' and
      fin
        .raw(sqlText)
        .then((result) => {
          res.send(result);
        })
        .catch((err) => {
          log.error(err);
          res.status(500).send(err);
        });
    }
  } else if (type == constants.BACKEND && userNew.admin == false) {
    if (!data || !data.NAME) {
      fin
        .find({ USER_ID_USER_ID: userNew.user_id })
        .then((result) => {
          res.send(result);
        })
        .catch((err) => {
          log.error(err);
          res.status(500).send(err);
        });
    } else {
      fin
        .raw(
          "select * from " +
          tableName +
          " where NAME like '%" +
          data.NAME +
          "%'" +
          " and USER_ID_USER_ID = '" +
          userNew.user_id +
          "'"
        )
        .then((result) => {
          res.send(result);
        })
        .catch((err) => {
          log.error(err);
          res.status(500).send(err);
        });
    }
  }
});

router.post("/add", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '新建Fin'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/fin_add"
                      }  
                  
                  
                  
                    },
              }
      } */
  const { data } = req.body;

  const user = checkAuth(req.user, constants.FINAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
    return;
  }

  //delete shit 

  delete data.LOAN_DATE;
  data.USER_ID_USER_ID = user.user_id;
  data.USER_NAME = user.user_name;
  data.FIN_ID = uunewid.v4();
  data.UPDATED_AT= moment().format("YYYY-MM-DD");
  data.CREATED_AT= moment().format("YYYY-MM-DD");

  insertData(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      // log.error(err);
      console.log(err);
      res.status(500).send(err);
    });
});

async function insertData(body) {

  Object.keys(body).forEach((item) => {
    if (body[item] == ""||body[item]==null) delete body[item];
  });
  const user = new ohana(tableName);
  try {
    const result = await user.insert(body);
    return result;
  } catch (error) {
    console.log(error)
    // log.error(error);
    return -1;
  }
}

/**
 * Get the company by criterian
 */
router.post("/search", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '搜索Fin，query参数未知'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  const queryData = req.query;
  const user = new ohana(tableName); // new ohana('table_name');
  if (queryData == null) {
    user
      .find()
      .then((result) => {
        res.send(result);
      })
      .catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
  } else {
    user
      .find(queryData)
      .then((result) => {
        res.send(result);
      })
      .catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
  }
});

/**update fins  */
router.put("/", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '修改Fin'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/fin_update"
                      }  
                  },
              }
      } */
  //   checkData(res, req.body.data)
  const { data } = req.body;
  delete data.LOAN_DATE;
  delete data.tags;
  data.UPDATED_AT= moment().format("YYYY-MM-DD");
  updateFin(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(500);
      }
      //return res.send(result)
    })
    .catch((err) => {
      console.log(err)
      // log.error(err);
      res.status(500).send(err);
    });
});

/*delete something*/
router.delete("/", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '删除Fin'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/fin_delete"
                      }  
                  },
              }
      } */
  checkData(res, req.body.data);
  const { data } = req.body;
  deleteFin(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function updateFin(body) {
  const policy = new ohana(tableName); // new ohana('table_name');
  const result = await policy.update(
    {
      FIN_ID: body.FIN_ID,
    },
    body
  );
  return result;
}
async function deleteFin(body) {
  const policy = new ohana(tableName);
  try {
    const result = await policy.raw(
      "delete from " + tableName + " where FIN_ID = '" + body.FIN_ID + "'"
    );
    policy.raw(
      "delete from " +
      constants.RECOMMENDLISTTABLE +
      " where RECOMMENDED_ID ='" +
      body.FIN_ID +
      "'"
    );
    policy.raw(
      "delete from " +
      constants.FAVORITETABLE +
      " where FAVOURITE_ID ='" +
      body.FIN_ID +
      "'"
    );
    return result;
  } catch (error) {
    log.error(error);
    return -1;
  }
}

/// fin tags

router.post("/finTags", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '获取标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/fin_finTags"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  getFinTag(data)
    .then((result) => {
      res.send(result);
      // if (result == 1) {
      //    res.send(result)
      // } else {
      //     res.send(500)
      // }
      //return res.send(result)
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/addTags", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '添加标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/fin_addTags"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  insertFinData(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else if (result == -1) {
        res.status(200).send({ code: -1, message: "请勿重复点击" })
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/deleteTags", function (req, res, next) {
  // #swagger.tags = ['金融惠企']
  // #swagger.summary = '删除标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/fin_deleteTags"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  deletaFinTag(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/**
 * 127.0.0.1:4000/api/users/getCompanyInfo
 * @param {*} body
 * @returns
 */
async function getFinTag(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  const result = await policyTag.raw(
    'select "TAG_ID", "TAG_NAME","TAG_VALUE" ,"TAG_CATEGORY" from "SAP_JIANGDU_TAGS" where "TAG_ID" in (Select "TAG_ID_TAG_ID" from "SAP_JIANGDU_TAG_FINS" where "FIN_ID_FIN_ID" = \'' +
    data.FIN_ID +
    "' )"
  );
  return result;
}
async function insertFinData(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  const resultNum = await policyTag.find(data);
  if (resultNum.length > 0) {
    return -1;
  }
  const result = await policyTag.insert(data);
  return result;
}
async function deletaFinTag(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  const result = await policyTag.raw(
    'delete from "' +
    tagTableName +
    "\" where FIN_ID_FIN_ID = '" +
    data.FIN_ID_FIN_ID +
    "' and TAG_ID_TAG_ID = '" +
    data.TAG_ID_TAG_ID +
    "'"
  );
  return result;
}

module.exports = router;
